package com.anolesoft.amljmes.fourteenDispatch.service.mapper;

import com.anolesoft.amljmes.fourteenDispatch.dto.ChuangTouJiHuaEmpDTO;
import com.anolesoft.amljmes.fourteenDispatch.dto.EmpDTO;
import com.anolesoft.amljmes.fourteenDispatch.dto.MesPlanDispatchingDto;
import com.anolesoft.amljmes.fourteenDispatch.dto.MesProcessQlbzDO;
import org.apache.ibatis.annotations.*;

import java.time.LocalDate;
import java.util.List;

@Mapper
public interface MesPlanDispatchingServiceMapper {
    //根据制造记录条码查询
	@Select("select rownum no, t.*\n" +
			"  from (select a.objuid,\n" +
			"               a.pacr_no,\n" +
			"               a.jixing,\n" +
			"               a.tuhao,\n" +
			"               a.paigonghao,\n" +
			"               a.gongxuid,\n" +
			"               a.gongxuname,\n" +
			"               a.gongxumark,\n" +
			"               a.gongxuwhgt,\n" +
			"               a.paigongtime,\n" +
			"               a.state,\n" +
			"               a.factory_id,\n" +
			"               a.process_trace_obj,\n" +
			"               a.checktime,\n" +
			"               b.processstate,\n" +
			"               c.name              paigongren,\n" +
			"               d.name              caozuozhe,\n" +
			"               a.jiedanren\n" +
			"          from mes_plan_dispatch a\n" +
			"          left join mes_process_trace b\n" +
			"            on a.process_trace_obj = b.objuid\n" +
			"          left join stf_employee c\n" +
			"            on a.paigongren = c.objuid\n" +
			"          left join stf_employee d\n" +
			"            on a.caozuozhe = d.objuid\n" +
			"         where a.pacr_no = #{pacrNo, jdbcType = VARCHAR}\n" +
			"           and a.state = 0\n" +
			"           and b.processstate != 70\n" +
			"           and b.modsign not in ('d', 'n')\n" +
			"         order by case\n" +
			"                    when length(regexp_substr(b.processno,\n" +
			"                                              '^\\\\s*[[:digit:]]',\n" +
			"                                              1,\n" +
			"                                              1)) > 0 then\n" +
			"                     to_number(to_single_byte(regexp_substr(b.processno,\n" +
			"                                                            '^\\\\s*[[:digit:]]',\n" +
			"                                                            1,\n" +
			"                                                            1)))\n" +
			"                    else\n" +
			"                     case\n" +
			"                       when regexp_instr(b.processno, '[()\uF8F5\uF8F5]') > 0 then\n" +
			"                        to_number(regexp_replace(b.processno, '[()\uF8F5\uF8F5]', ''))\n" +
			"                       else\n" +
			"                        9999999\n" +
			"                     end\n" +
			"                  end,\n" +
			"                  case\n" +
			"                    when instr(b.processno, '-') > 0 then\n" +
			"                     to_number(substr(b.processno, instr(b.processno, '-') + 1))\n" +
			"                    else\n" +
			"                     0\n" +
			"                  end,\n" +
			"                  case\n" +
			"                    when instr(b.processno, '\uF8F5') > 0 then\n" +
			"                     to_number(substr(b.processno, instr(b.processno, '\uF8F5') + 1))\n" +
			"                    else\n" +
			"                     0\n" +
			"                  end,\n" +
			"                  case\n" +
			"                    when instr(b.processno, '') > 0 then\n" +
			"                     to_number(substr(b.processno, instr(b.processno, '') + 1))\n" +
			"                    else\n" +
			"                     0\n" +
			"                  end,\n" +
			"                  b.processno) t")
    List<MesPlanDispatchingDto> DispatchingSelectAll(String pacrNo);

    //批量清除
    @Delete("delete from mes_plan_dispatch where objuid = #{objuid, jdbcType=VARCHAR} ")
    int deleteDispatching(String objuid);

    //批量取消派工
    @Update("    update mes_plan_dispatch" +
            "      set state = 1,quxiaoren = #{quxiaoren, jdbcType=VARCHAR},quxiaotime = sysdate" +
            "    where objuid = #{objuid, jdbcType=VARCHAR}"
    )
    int updateDispatching(@Param("objuid") String objuid,@Param("quxiaoren") String quxiaoren);

    //根据制造记录条码查询
	@Select("select rownum no, t.*\n" +
			"  from (select a.fabrecordid  pacr_no,\n" +
			"               a.jx           jixing,\n" +
			"               a.partid       tuhao,\n" +
			"               a.assignmentid paigonghao,\n" +
			"               b.objuid,\n" +
			"               b.processno    gongxuid,\n" +
			"               b.processname  gongxuname,\n" +
			"               b.processdesc  gongxuwhgt,\n" +
			"               b.biaoji       gongxumark\n" +
			"          from mes_order_trace a, mes_process_trace b\n" +
			"         where a.objuid = b.ordertraceuid\n" +
			"           and a.fabrecordid = #{pacrNo, jdbcType=VARCHAR}\n" +
			"           and b.processstate != 70\n" +
			"           and b.modsign not in ('d', 'n')\n" +
			"         order by case\n" +
			"                    when length(regexp_substr(b.processno,\n" +
			"                                              '^\\s*[[:digit:]]+',\n" +
			"                                              1,\n" +
			"                                              1)) > 0 then\n" +
			"                     to_number(to_single_byte(regexp_substr(b.processno,\n" +
			"                                                            '^\\s*[[:digit:]]+',\n" +
			"                                                            1,\n" +
			"                                                            1)))\n" +
			"                    else\n" +
			"                     case\n" +
			"                       when regexp_instr(b.processno, '[()\uF8F5\uF8F5]') > 0 then\n" +
			"                        to_number(regexp_replace(b.processno, '[()\uF8F5\uF8F5]', ''))\n" +
			"                       else\n" +
			"                        9999999\n" +
			"                     end\n" +
			"                  end,\n" +
			"                  case\n" +
			"                    when instr(b.processno, '-') > 0 then\n" +
			"                     to_number(substr(b.processno,\n" +
			"                                      instr(b.processno, '-') + 1))\n" +
			"                    else\n" +
			"                     0\n" +
			"                  end,\n" +
			"                  case\n" +
			"                    when instr(b.processno, '\uF8F5') > 0 then\n" +
			"                     to_number(substr(b.processno,\n" +
			"                                      instr(b.processno, '\uF8F5') + 1))\n" +
			"                    else\n" +
			"                     0\n" +
			"                  end,\n" +
			"                  case\n" +
			"                    when instr(b.processno, '') > 0 then\n" +
			"                     to_number(substr(b.processno, instr(b.processno, '') + 1))\n" +
			"                    else\n" +
			"                     0\n" +
			"                  end,\n" +
			"                  b.processno) t\n")
    List<MesPlanDispatchingDto> orderSelectAll(String pacrNo);

    // 添加
    @Insert("    insert into mes_plan_dispatch"
            + "      (pacr_no, jixing, tuhao, paigonghao," +
            " gongxuid, gongxuname, gongxumark, gongxuwhgt,paigongren, " +
            "paigongtime, caozuozhe, factory_id, process_trace_obj, checktime )" + "    values"
            + "      (#{pacrNo, jdbcType=VARCHAR},#{jixing, jdbcType=VARCHAR},#{tuhao, jdbcType=VARCHAR},#{paigonghao, jdbcType=VARCHAR},"
            + "#{gongxuid, jdbcType=VARCHAR}, #{gongxuname, jdbcType=VARCHAR},#{gongxumark, jdbcType=VARCHAR},#{gongxuwhgt, jdbcType=VARCHAR}, #{paigongren, jdbcType=VARCHAR}, "
            + "sysdate, #{caozuozhe, jdbcType=VARCHAR},#{factoryId, jdbcType=VARCHAR},#{objuid, jdbcType=VARCHAR},to_date(#{checktimeStr, jdbcType=DATE},'yyyy-mm-dd'))")
    int addDispatching(MesPlanDispatchingDto inFoDtoParam);

//    @Select("select eid from view_chuangtoujihua_emp where objuid = #{objuid, jdbcType=VARCHAR}")
//    ChuangTouJiHuaEmpDTO queryEmpEid(String objuid);
//  胸卡号
    @Select("select objuid,eid,name from view_chuangtoujihua_emp where eid!='admin'")
    List<ChuangTouJiHuaEmpDTO> queryEmpEid();

	@Select("select factory_id from stf_employee where delete_flag=0 and objuid=#{objuid, jdbcType=VARCHAR}")
	EmpDTO queryById(String objuid);

	@Update("    update mes_process_trace" +
			"      set processstate = 40" +
			"    where objuid = #{objuid, jdbcType=VARCHAR}"
	)
	int updateprocessstate(String objuid);

	/**
	 * 接单
	 * @param inFoDtoParam
	 * @return
	 */
	@Insert("insert into mes_plan_dispatch\n" +
			" (pacr_no, jixing, tuhao, paigonghao, gongxuid, gongxuname, gongxumark, gongxuwhgt,jiedanren,\n" +
			" jiedantime, caozuozhe, factory_id, process_trace_obj, checktime, planstarttime, planendtime, urgency,plantime)\n" +
			"values \n" +
			" (#{pacrNo, jdbcType=VARCHAR},#{jixing, jdbcType=VARCHAR},#{tuhao, jdbcType=VARCHAR},#{paigonghao, jdbcType=VARCHAR},\n" +
			" #{gongxuid, jdbcType=VARCHAR}, #{gongxuname, jdbcType=VARCHAR},#{gongxumark, jdbcType=VARCHAR}," +
			" #{gongxuwhgt, jdbcType=VARCHAR}, #{jiedanren, jdbcType=VARCHAR}, sysdate, #{caozuozhe, jdbcType=VARCHAR}," +
			" #{factoryId, jdbcType=VARCHAR},#{objuid, jdbcType=VARCHAR},#{checktime, jdbcType=DATE}," +
			" #{planstarttime, jdbcType=DATE},#{planendtime, jdbcType=DATE},#{urgency, jdbcType=NUMERIC}," +
			" (trunc(sysdate)+floor(to_number(#{qiliang, jdbcType=VARCHAR}))))")
	int jiedan(MesPlanDispatchingDto inFoDtoParam);

	/**
	 * 重复接单时更新数据
	 * @param inFoDtoParam
	 * @return
	 */
	@Update("update mes_plan_dispatch a\n" +
			"set a.jiedanren = #{jiedanren, jdbcType=VARCHAR},\n" +
			"a.caozuozhe = #{caozuozhe, jdbcType=VARCHAR},\n" +
			"a.plantime = trunc(sysdate)+floor(to_number(#{qiliang, jdbcType=VARCHAR})),\n" +
			"a.jiedantime = sysdate\n" +
			"where a.objuid = #{objuid, jdbcType=VARCHAR}")
	int updateJiedan(MesPlanDispatchingDto inFoDtoParam);

	/**
	 * 查看制造记录是否为临时计划 用于获取考核日期
	 * 2022-08-29 zyq
	 * @param pacrNo
	 * @return
	 */
	@Select("select a.isfromtemp from mes_order_trace a where a.fabrecordid = #{pacrNo, jdbcType=VARCHAR}")
	int isfromtemp(String pacrNo);

	/**
	 * 获取批次计划计划完工日期（考核日期前一天） 用于计算每道工序计划开始时间和计划完成时间
	 * 2022-08-29 zyq
	 * @param pacrNo
	 * @return
	 */
	@Select("select b.kaohedate-1 from " +
			"(select a.plnuid from mes_order_trace a where a.fabrecordid = #{pacrNo, jdbcType=VARCHAR}) a\n" +
			"inner join pln_order b on a.plnuid = b.objuid")
	LocalDate checktimePlan(String pacrNo);

	/**
	 * 获取临时计划计划完工日期（考核日期前一天） 用于计算每道工序计划开始时间和计划完成时间
	 * 2022-08-29 zyq
	 * @param pacrNo
	 * @return
	 */
	@Select("select b.stoptime-1 from " +
			"(select a.plnuid from mes_order_trace a where a.fabrecordid = #{pacrNo, jdbcType=VARCHAR}) a" +
			" inner join mes_plan_lsjh b on a.plnuid = b.objuid")
	LocalDate checktimeTemp(String pacrNo);

	/**
	 * 获取期量时间相关信息 用于计算工序计划开始时间和计划完成时间
	 * 2022-08-29 zyq
	 * @param pacrNo
	 * @param factoryId
	 * @return
	 */
	@Select("select b.objuid, b.partid, b.gongxuid, b.gongxuname, b.qiliangtime from\n" +
			" (select a.partid from mes_order_trace a where a.fabrecordid = #{pacrNo, jdbcType=VARCHAR}) a\n" +
			" inner join (select * from mes_process_qlbz b where b.factory_id = #{factoryId, jdbcType=VARCHAR}" +
			" and b.delete_flag = 0) b " +
			" on a.partid = b.partid")
	List<MesProcessQlbzDO> processQlbz(@Param("pacrNo") String pacrNo, @Param("factoryId") String factoryId);
}
